package com.nz.database.init;

import cn.hutool.core.util.StrUtil;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.support.BeanDefinitionBuilder;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.init.DatabasePopulatorUtils;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

/**
 * @program: springboot
 * @description:
 * @author: 翁仕权
 * @create: 2020-06-06 16:27
 */

@SuppressWarnings("unused")
public abstract class DbExecuteUtils {
    private static final Logger logger = LoggerFactory.getLogger(DbExecuteUtils.class);
    // 系统基础数据
    private static final String DATA_SCOPE_BASE = "base";
    // 系统生产数据
    private static final String DATA_SCOPE_PRODUCT = "product";
    // 系统演示数据
    private static final String DATA_SCOPE_DEMO = "demo";
    // 收尾
    private static final String DATA_SCOPE_COMPLETE = "complete";


    /**
     * 初始化数据库   不含补丁
     * @param databaseType
     * @param method
     * @param dataScope
     * @param continueOnError
     * @param dbCompany
     */
    public static void initDataBase(String databaseType,String hostIp, String port, String instance,
                                    String user, String password, String driver,
                                    String method, String dataScope, String continueOnError,String dbCompany) {
        String pathDir = DbInit.class.getResource("").getPath();
        String rootPathDir = DbInit.class.getResource("/").getPath();
        logger.info("path:"+pathDir);
        logger.info("root path:"+rootPathDir);
        List<String> sqlPathList = new ArrayList<String>();
        List<String> sqlFolderList = new ArrayList<String>();

        // 建表、建用户 、等语句授权
        sqlPathList.add(String.format("%s%s/%s%s", pathDir, databaseType, "prehandle","/0001_mysql-clean.sql"));
        sqlPathList.add(String.format("%s%s/%s%s", pathDir, databaseType, "prehandle","/0002_mysql-database.sql"));
        sqlPathList.add(String.format("%s%s/%s%s", pathDir, databaseType, "prehandle","/0003_create-user.sql"));
        sqlPathList.add(String.format("%s%s/%s%s", pathDir, databaseType, "prehandle","/0004_01_mysql-phoenix-table.sql"));
        sqlPathList.add(String.format("%s%s/%s%s", pathDir, databaseType, "prehandle","/0004_02_mysql-irrigate-table.sql"));
        sqlPathList.add(String.format("%s%s/%s%s", pathDir, databaseType, "prehandle","/0005_mysql-grant.sql"));

        // 基础数据
        sqlFolderList.add(String.format("%s/%s/%s", pathDir, "data", DATA_SCOPE_BASE));

        // 系统演示数据
        if (!StrUtil.isEmpty(dbCompany)) {
            sqlFolderList.add(String.format("%s/%s/%s/%s", pathDir, "data", "company", dbCompany));
        }

        // 刷库收尾脚本
        sqlFolderList.add(String.format("%s/%s/%s", pathDir, databaseType, DATA_SCOPE_COMPLETE));
        for (String folder : sqlFolderList) {
            getResourceDatabasePopulatorFiles(folder, sqlPathList);
        }
        buildXml(sqlPathList, null, databaseType, pathDir,method);

        String dbUri = String.format("jdbc:%s://%s:%s/%s",databaseType,hostIp,port,instance);
        if(databaseType.equals("mysql")){
            dbUri = dbUri + "?characterEncoding=utf8&characterSetResults=utf8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=UTC";
        }
        executeSql(sqlFolderList, sqlPathList, continueOnError,pathDir, rootPathDir, dbUri, driver, user,password);
    }

    /**
     * 执行  SQL 程序
     * @param sqlFolderList
     * @param sqlPathList
     * @param continueOnError
     * @param pathDir
     */
    //@SuppressWarnings("resource")
    public static void executeSql(List<String> sqlFolderList,List<String>sqlPathList,String continueOnError,String pathDir,String rootPathDir,
                                  String dbUri, String driver,String user, String password){
        String[] xmlfiles = new String[] { "classpath*:applicationContext.xml" };
        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(xmlfiles);

        ConfigurableApplicationContext configurableApplicationContext = (ConfigurableApplicationContext) ctx;
        // 获取bean工厂并转换为DefaultListableBeanFactory
        DefaultListableBeanFactory defaultListableBeanFactory = (DefaultListableBeanFactory) configurableApplicationContext.getBeanFactory();
        // 通过BeanDefinitionBuilder创建bean定义
        BeanDefinitionBuilder beanDefinitionBuilder = BeanDefinitionBuilder.genericBeanDefinition(HikariDataSource.class);
        beanDefinitionBuilder.addPropertyValue("driverClassName", driver);
        beanDefinitionBuilder.addPropertyValue("jdbcUrl", dbUri);
        beanDefinitionBuilder.addPropertyValue("username", user);
        beanDefinitionBuilder.addPropertyValue("password", password);
        // 注册bean
        defaultListableBeanFactory.registerBeanDefinition("dataSource", beanDefinitionBuilder.getRawBeanDefinition());

        DataSource dataSource = ctx.getBean(HikariDataSource.class);
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        if(continueOnError!=null&&continueOnError.trim().equalsIgnoreCase("true")){
            populator.setContinueOnError(true);
        }
        for (String sqlFile : sqlPathList) {
            populator.addScript(ctx.getResource(String.format("classpath:%s",sqlFile.substring(rootPathDir.length()-1))));
        }
        DatabasePopulatorUtils.execute(populator, dataSource);
    }


    /**
     * 构建  xml 文件
     * @param sqlPathList
     * @param dataScopeSet
     * @param databaseType
     * @param pathDir
     * @param method
     */
    public static void buildXml(List<String> sqlPathList, Set<String> dataScopeSet, String databaseType,
                                String pathDir, String method) {
        String rootPathDir = DbInit.class.getResource("/").getPath();
        StringBuilder sb=new StringBuilder();
        sb.append(rootPathDir +  method+"-");
        sb.append(databaseType);
        if(dataScopeSet != null) {
            for(String dataScope:dataScopeSet){
                sb.append("-"+ dataScope);
            }
        }
        sb.append(".xml");
        String fileName=sb.toString();
        try {

            // 追加
            File file =new File(fileName);
            if(file.exists()) {
                file.delete();
            }

            FileWriter fw = new FileWriter(fileName, true);
            PrintWriter out = new PrintWriter(fw);
            String jdbc_script = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
            out.println(jdbc_script);
            jdbc_script = "<beans xmlns=\"http://www.springframework.org/schema/beans\""
                    + " xmlns:jdbc=\"http://www.springframework.org/schema/jdbc\""
                    + " xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\""
                    + " xsi:schemaLocation=\"http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"
                    + " http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd\">";
            out.println(jdbc_script);
            out.println("");
            jdbc_script="	<import resource=\"classpath*:applicationContext-dataSouce.xml\" />";
            out.println(jdbc_script);
            out.println("");
            jdbc_script="	<import resource=\"classpath*:applicationContext-propertyConfigurer.xml\" />";
            out.println(jdbc_script);
            out.println("");
            jdbc_script=String.format("	<jdbc:initialize-database data-source=\"dataSource\" ignore-failures=\"%s\">","ALL");
            out.println(jdbc_script);
            out.println("");
            for (String sqlFile : sqlPathList) {
                jdbc_script=String.format("		<!-- 文件名为  %s -->", sqlFile.substring(sqlFile.lastIndexOf("\\")+1));
                out.println(jdbc_script);
                jdbc_script = String.format("		<jdbc:script location=\"%s\"/>", String.format("classpath:%s",sqlFile.substring(pathDir.length()-1)).replace("\\", "/"));
                out.println(jdbc_script);
                out.println("");
            }
            jdbc_script="	</jdbc:initialize-database>";
            out.println(jdbc_script);
            out.println("");
            jdbc_script="</beans>";
            out.println(jdbc_script);
            out.println("");
            out.close();
            fw.close();
            logger.info("生成以xml 方式初始化的文件名为："+fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }finally{

        }
    }

    /**
     * 递归读取目录下的 Sql
     *
     * @param pathDir
     * @param sqlPathList
     */
    public static void getResourceDatabasePopulatorFiles(String pathDir, List<String> sqlPathList) {
        File[] testFile = new File(pathDir).listFiles();
        if(testFile==null){
            return;
        }
        for (int i = 0; i < testFile.length; i++) {
            if (testFile[i].isFile() && testFile[i].getName().endsWith(".sql")) {
                //System.out.println(testFile[i].getAbsolutePath());
                sqlPathList.add(testFile[i].getAbsolutePath());
            } else if (testFile[i].isDirectory()) {
                //System.out.println("文件目录："+testFile[i].getAbsolutePath());
                getResourceDatabasePopulatorFiles(testFile[i].getPath(), sqlPathList);
            } else {
                //System.out.println("非SQL文件不处理："+testFile[i].getName());
            }
        }
    }
}
